--PREGUNTA 1:
SELECT p.Nombre, p.ApellidoPaterno, p.ApellidoMaterno, car.Nombre AS Carrera,c.NotaFinal
	FROM Persona AS p
	JOIN Cursa AS c ON p.Codigo = c.CodigoAlumno
	JOIN Materia AS m ON c.IdMateria = m.Id
	JOIN Carrera AS car ON m.IdCarrera = car.Id
	WHERE NotaFinal > 50
--PREGUNTA 2:
--Marco no te rias mucho de este super query hahaha, pero se me fundio mi cerebro la verdad y es la unica solucion que se me ocurre hehe
SELECT a1.Nombre, a1.ApellidoPaterno, a1.ApellidoMaterno, a1.Carrera, a1.NotaFinal, IIF(a1.NotaFinal>50,'Aprobado','Reprobado')
FROM(SELECT TOP 2 p.Nombre, p.ApellidoPaterno, p.ApellidoMaterno, car.Nombre AS Carrera,c.NotaFinal
	FROM Persona AS p
	JOIN Cursa AS c ON p.Codigo = c.CodigoAlumno
	JOIN Materia AS m ON c.IdMateria = m.Id
	JOIN Carrera AS car ON m.IdCarrera = car.Id
	WHERE car.Id = 1
	ORDER BY c.NotaFinal ASC) AS a1
UNION
SELECT a2.Nombre, a2.ApellidoPaterno, a2.ApellidoMaterno, a2.Carrera, a2.NotaFinal, IIF(a2.NotaFinal>50,'Aprobado','Reprobado')
FROM(SELECT TOP 2 p.Nombre, p.ApellidoPaterno, p.ApellidoMaterno, car.Nombre AS Carrera,c.NotaFinal
	FROM Persona AS p
	JOIN Cursa AS c ON p.Codigo = c.CodigoAlumno
	JOIN Materia AS m ON c.IdMateria = m.Id
	JOIN Carrera AS car ON m.IdCarrera = car.Id
	WHERE car.Id = 1
	ORDER BY c.NotaFinal DESC) AS a2
UNION
SELECT a3.Nombre, a3.ApellidoPaterno, a3.ApellidoMaterno, a3.Carrera, a3.NotaFinal, IIF(a3.NotaFinal>50,'Aprobado','Reprobado')
FROM(SELECT TOP 2 p.Nombre, p.ApellidoPaterno, p.ApellidoMaterno, car.Nombre AS Carrera,c.NotaFinal
	FROM Persona AS p
	JOIN Cursa AS c ON p.Codigo = c.CodigoAlumno
	JOIN Materia AS m ON c.IdMateria = m.Id
	JOIN Carrera AS car ON m.IdCarrera = car.Id
	WHERE car.Id = 2
	ORDER BY c.NotaFinal ASC) AS a3
UNION
SELECT a4.Nombre, a4.ApellidoPaterno, a4.ApellidoMaterno, a4.Carrera, a4.NotaFinal, IIF(a4.NotaFinal>50,'Aprobado','Reprobado')
FROM(SELECT TOP 2 p.Nombre, p.ApellidoPaterno, p.ApellidoMaterno, car.Nombre AS Carrera,c.NotaFinal
	FROM Persona AS p
	JOIN Cursa AS c ON p.Codigo = c.CodigoAlumno
	JOIN Materia AS m ON c.IdMateria = m.Id
	JOIN Carrera AS car ON m.IdCarrera = car.Id
	WHERE car.Id = 2
	ORDER BY c.NotaFinal DESC) AS a4
UNION
SELECT a5.Nombre, a5.ApellidoPaterno, a5.ApellidoMaterno, a5.Carrera, a5.NotaFinal, IIF(a5.NotaFinal>50,'Aprobado','Reprobado')
FROM(SELECT TOP 2 p.Nombre, p.ApellidoPaterno, p.ApellidoMaterno, car.Nombre AS Carrera,c.NotaFinal
	FROM Persona AS p
	JOIN Cursa AS c ON p.Codigo = c.CodigoAlumno
	JOIN Materia AS m ON c.IdMateria = m.Id
	JOIN Carrera AS car ON m.IdCarrera = car.Id
	WHERE car.Id = 3
	ORDER BY c.NotaFinal ASC) AS a5
UNION
SELECT a6.Nombre, a6.ApellidoPaterno, a6.ApellidoMaterno, a6.Carrera, a6.NotaFinal, IIF(a6.NotaFinal>50,'Aprobado','Reprobado')
FROM(SELECT TOP 2 p.Nombre, p.ApellidoPaterno, p.ApellidoMaterno, car.Nombre AS Carrera,c.NotaFinal
	FROM Persona AS p
	JOIN Cursa AS c ON p.Codigo = c.CodigoAlumno
	JOIN Materia AS m ON c.IdMateria = m.Id
	JOIN Carrera AS car ON m.IdCarrera = car.Id
	WHERE car.Id = 3
	ORDER BY c.NotaFinal DESC) AS a6
--PREGUNTA 3:
;WITH p2 AS(
	SELECT COUNT(CodigoAlumno) AS AR, IdMateria
		FROM Cursa
		WHERE NotaFinal < 51
		GROUP BY IdMateria
)
SELECT AR, m.Nombre
	FROM p2 AS o
	JOIN Materia AS m ON IdMateria = m.Id
	WHERE AR = (
		SELECT MAX(AR)
			FROM p2 AS d
	)
--PREGUNTA 4:
--EN ESTA PREGUNTA NO ESTOY SEGURO DEBIDO A QUE CADA ALUMNO TOMA 1 MATERIA HACIENDO QUE MUESTRE TODO
;WITH p4 AS(
	SELECT p.Codigo, c.NotaFinal, p.ApellidoPaterno + ' ' + p.ApellidoMaterno + ' ' + p.Nombre AS Nombre, m.Nombre AS Materia
		FROM Persona AS p
		JOIN Cursa AS c ON p.Codigo = c.CodigoAlumno
		JOIN Materia AS m ON c.IdMateria = m.Id
		JOIN Carrera AS car ON m.IdCarrera = car.Id
)
SELECT *
	FROM p4 AS o
	WHERE NotaFinal = (
		SELECT MIN(d.NotaFinal)
			FROM p4 as d
			WHERE o.Codigo = d.Codigo)
--PREGUNTA 5:
;WITH p5 AS(
	SELECT c.NotaFinal, p.ApellidoPaterno + ' ' + p.ApellidoMaterno + ' ' + p.Nombre AS Nombre, c.Semestre, car.Id
		FROM Persona AS p
		JOIN Cursa AS c ON p.Codigo = c.CodigoAlumno
		JOIN Materia AS m ON c.IdMateria = m.Id
		JOIN Carrera AS car ON m.IdCarrera = car.Id
)
SELECT *
	FROM p5 AS o
	WHERE NotaFinal = (
		SELECT MAX(d.NotaFinal)
			FROM p5 as d
			WHERE o.Id = d.Id
			AND o.Semestre = d.Semestre)
--PREGUNTA 6:
DECLARE @n INT
SET @n = 5--<=NUMERO INGRESADO POR EL USUARIO
SELECT ROW_NUMBER() OVER(ORDER BY ApellidoPaterno), ApellidoPaterno, ApellidoMaterno, Nombre
	FROM Persona
	ORDER BY ApellidoPaterno ASC OFFSET 10*(@n-1) ROWS FETCH NEXT 10 ROWS ONLY;--EMPEZANDO LA PAGINACION EN 1
	--ORDER BY ApellidoPaterno ASC OFFSET 10*(@n) ROWS FETCH NEXT 10 ROWS ONLY;--EMPEZANDO LA PAGINACION EN 0